package dao

import (
	"fmt"
	"github.com/astaxie/beego/orm"
	"analysis-ss/models"
)

func QueryCourse(childId interface{}, courseId string, start, length int) ([]models.ScheduleDetailChild, int64, error) {
	var courses []models.ScheduleDetailChild
	o := orm.NewOrm()
	o.Using("default")
	sqlStr := `SELECT
                   sd.child_id ,
                   sp.projects ,
                   sd.schedule_detail_id AS id,
                   cs.course_id,
                   cs.course_name,
                   cs.start_time,
                   cs.end_time,
                   cs.week,
                   sd.teacher_name,
                   st.sign_type
                    FROM
                   schedule_detail_child sd
                   LEFT JOIN
                   schedule_child_projects sp ON sd.schedule_detail_id = sp.schedule_id
                   AND sd.child_id = sp.child_id
                   LEFT JOIN
                   class_schedule_detail cs ON sd.schedule_detail_id = cs.id
                   LEFT join
                   t_child_schedule_status st on st.schedule_id=sd.schedule_detail_id and  st.child_id=sd.child_id
                   WHERE
                   sd.child_id = ? `
	if courseId != "0" && courseId != "" {
		sqlStr = fmt.Sprint(sqlStr, " and  cs.course_id =", courseId)
	}
	sqlStr = fmt.Sprint(sqlStr, " order by  cs.start_time desc limit ", start, ",", length)
	num, err := o.Raw(sqlStr, childId).QueryRows(&courses)
	return courses,num, err
}

func QueryCourseScore(childId interface{}, scheduleId interface{}) ([]models.Score, int64, error) {
	var Scores []models.Score
	o := orm.NewOrm()
	o.Using("default")
	sqlStr := `SELECT
                   s.id, si.name, si.questions, s.score, s.memo
                   FROM
                   score s
                   LEFT JOIN
                   sensory_integration si ON si.code = s.project_code
                   WHERE
                   s.child_id = ?
                   AND s.schedule_id = ?;`
	num, err := o.Raw(sqlStr, childId, scheduleId).QueryRows(&Scores)
	return Scores, num, err
}

func QueryCourseTotal(childId interface{},courseId string) (int, error) {
	var Total int
	o := orm.NewOrm()
	o.Using("default")
	sqlStr := `SELECT
                   COUNT(*)
                   FROM
                   schedule_detail_child sd
                   LEFT JOIN
                   class_schedule_detail cs ON sd.schedule_detail_id = cs.id
                   WHERE
                   sd.child_id =?  `
	if courseId != "0" && courseId != "" {
		sqlStr = fmt.Sprint(sqlStr, " and  cs.course_id =", courseId)
	}
	err := o.Raw(sqlStr,childId).QueryRow(&Total)
	return Total, err
}

func QueryChildCode(childId interface{}) ([]models.ChildAbilityFeedbackConfig, error) {
	var codeNames []models.ChildAbilityFeedbackConfig
	o := orm.NewOrm()
	o.Using("default")
	sqlStr := `SELECT DISTINCT(p.id),p.code,p.name
             FROM
                t_project p
             LEFT JOIN
                t_schedule_child_project sc ON p.id =sc.project_id
             WHERE
                sc.child_id = ? AND p.is_delete!=1 AND sc.is_delete!=1; `
	_, err := o.Raw(sqlStr, childId).QueryRows(&codeNames)
	return codeNames, err
}

// 查找孩子的测评信息
func QueryChildRecord(childId interface{}) (models.StudyAbilityReport, error) {
	var Record models.StudyAbilityReport
	o := orm.NewOrm()
	o.Using("record")
	sqlStr := `SELECT
	        stu_id as id,
		qiantingpingheng,
		bentigan,
		chujuefangyu,
		tingjuejiyiguangdu,
		tingjuechangshijiyi,
		shifenbianshiwulv,
		shijueguangdu,
		shidongtonghenengli,
		yuedusudu,
		cuowulv,
		yuedulijiezhengquelv,
		kouyubiaodanengli,
		siweifenshu,
		baifendengji
	        FROM
	        study_ability_report
	        where
	        stu_id = ?;`
	err := o.Raw(sqlStr, childId).QueryRow(&Record)
	return Record, err
}

// 查看孩子的所有的配置
func QueryChildCodeConfig(childId,code interface{}) ([]models.ChildAbilityFeedbackConfig, error) {
	var configs []models.ChildAbilityFeedbackConfig
	o := orm.NewOrm()
	o.Using("default")
	sqlStr := `SELECT
                  c.id, c.child_id, c.code, c.name, c.value, s.name as code_name
                  FROM
                  child_ability_feedback_config c
                  LEFT JOIN
                  sensory_integration s ON s.code = c.code
                   WHERE
                   c.child_id = ? AND c.code = ?;`
	_, err := o.Raw(sqlStr, childId, code).QueryRows(&configs)
	return configs, err
}

// 查看孩子的配置
func QueryChildConfig(childId,code,codeStr interface{}) ([]models.ChildAbilityFeedbackConfig, error) {
	var configs []models.ChildAbilityFeedbackConfig
	o := orm.NewOrm()
	o.Using("default")
	sqlStr := `SELECT
                   id, child_id, code, name, value
                   FROM
                   child_ability_feedback_config
                   WHERE
                   child_id = ?
                   AND code in (?,?);`
	_, err := o.Raw(sqlStr, childId, codeStr, code).QueryRows(&configs)
	return configs, err
}

// 查看孩子的项目的训练次数
func QueryChildCodeXLCS(childId,code interface{}) (string, error) {
	var times string
	o := orm.NewOrm()
	o.Using("default")
	sqlStr := `select
                count(*)
              from
                  t_schedule_project_score
              where child_id = ? and  project_code = ? and is_delete!=1`
	err := o.Raw(sqlStr,childId,code).QueryRow(&times)
	return times, err
}
